Temporal database access provides the capability to access data from a database, that reflects the state of the data at any given time in the life of the data. Temporal database access can support recovery at all levels, including the row, transaction, table, tablespace, and database wide levels, and is often used to correct human errors by reconstructing data as the data was in the past.
Temporal database access technology has evolved over the years. In an early incarnation, temporal access is enabled for a user session, through which a user can “see” the entire database at a particular point in time in the past (referred to as a “flashback query”). Hence, a user can access the state of all the data in a database at any specified point in time. This technology allows a user to query data as it was in the past, without having to persistently rollback changes that were made to the data from the time in the past up to the time of the query (which can take hours or days to complete). Thus, this incarnation allows for a speedy method for point-in-time recovery, like having a rewind button for the database.
Unlike conventional queries, flashback queries specify a flashback time, and return data as it existed at the specified flashback time. One technique for handling flashback queries is described in U.S. Pat. No. 6,631,374 entitled “System And Method For Providing Fine-Grained Temporal Database Access” by Jonathan D. Klein, et al., the contents of which is incorporated by this reference in its entirety for all purposes as if fully set forth herein.
In another incarnation, temporal access enables a user to query particular data at multiple particular points in time in the past. For example, a user can query and manipulate data from a table T1 at a time t1, data from a table T2 at a time t2, data from a table T3 at present time, and so on, while maintaining referential integrity constraints among the tables. Thus, the granularity of the temporal access evolved from entire database access at a particular time in the past, to particular table access at particular points in time in the past, like having a rewind button for each of the tables in the database.
One technique for identifying one or more transactions that have modified or created an object, such as a row of data, in a database system is described in U.S. patent application Ser. No. 10/364,209 filed by Ken Jacobs, et al. on Feb. 10, 2003, entitled “Method And Mechanism For Identifying Last Transaction On A Row of Data”; which incorporates by reference U.S. patent application Ser. No. 09/748,408 filed by Amit Ganesh, et al. on Dec. 22, 2000 and issued as U.S. Pat. No. 6,647,510, entitled “Method and Apparatus for Making Available Data that was Locked by a Dead Transaction before Rolling Back the Entire Dead Transaction”; the contents of all of which are incorporated by this reference in their entirety for all purposes as if fully set forth herein.
One technique for undoing, reversing, or compensating for a committed transaction in a database system is described in U.S. patent application Ser. No. 10/364,065 filed by Amit Ganesh, et al. on Feb. 10, 2003, entitled “Method And Mechanism For Rolling Back A Transaction On A Row of Data”, the contents of which is incorporated by this reference in its entirety for all purposes as if fully set forth herein.
A related technique, for handling a “flashback cursor” that is created in response to receipt of a “flashback query” and that is a particular type of cursor that is used to access past data is described in U.S. patent application Ser. No. 10/325,211 filed by Bipul Sinha, et al. on Dec. 18, 2002, entitled “Method And Apparatus For Accessing Data As It Existed At A Previous Point In Time”, the contents of which is incorporated by this reference in its entirety for all purposes as if fully set forth herein.
In another incarnation, temporal access enables a user to query particular data over a range of time, rather than at a point in time. A user can query and manipulate multiple versions of data at the row level. Hence, the reach of the temporal access evolved from accessing the state of data at a point in time to the progression of data over a span of time. For example, in response to a “version” query, transaction metadata is returned for transactions that occurred on the specified data over the specified range of time, such as metadata that identifies what transactions were committed and when. Using this technology, one can pinpoint when and how data is changed and trace it back to the user, application, or transaction that changed the data.
Available techniques for implementing row versioning, which enables accessing and manipulating multiple versions of a row of a database table, including one or more past versions, are described in U.S. Provisional Patent Application No. 60/500,509, entitled “System, Structure, Interface, and Semantics For Implementing Row Versions: Accessing Past Versions Of A Data Item” and U.S. Provisional Patent Application No. 60/500,510, entitled “Method and Mechanism For Row Versioning”.
The current state of temporal database access is extended to provide fast and easy recovery at the database, table, row, and transaction level, and by just operating on the changed data. Therefore, data can be queried as it existed in the past, current data can be compared to past data, and deleted or changed data can be recovered. The time it takes to recover from an error is now equal to the amount of time it took to make the error. However, even in light of the existing capabilities regarding temporal access to data in a database, and the benefits provided thereby, there is room for improvement in methods for temporal access to data.